<?php
/**
* エラーチェッククラス
**/
require_once( '../system/Class_M_DAO.php' );
class Class_M_COST_DM extends Class_M_DAO {
	//protected properties begin
	/**
	 *
	 * テーブル名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_table  = "M_COST_DM";
	
	/**
	 *
	 * 主キー名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_pkey   = "id";
	
	/**
	 *
	 * ソート順
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_order = "id";
	
	protected $_cols  = array(
		"matrix_cd"     => array(AOKI_VARTYPE_STR),
		"begin_date"    => array(AOKI_VARTYPE_INT),
		"end_date"      => array(AOKI_VARTYPE_INT),
		"dm_date"       => array(AOKI_VARTYPE_INT),
		"dm_no"         => array(AOKI_VARTYPE_STR),
		"title"         => array(AOKI_VARTYPE_STR),
		"sort"          => array(AOKI_VARTYPE_INT),
		"haga_out"      => array(AOKI_VARTYPE_STR),
		"haga_in"       => array(AOKI_VARTYPE_STR),
		"futou"         => array(AOKI_VARTYPE_STR),
		"preken_out"    => array(AOKI_VARTYPE_STR),
		"preken_in"     => array(AOKI_VARTYPE_STR),
		"wariken_out"   => array(AOKI_VARTYPE_STR),
		"wariken_in"    => array(AOKI_VARTYPE_STR),
		"atemei_out"    => array(AOKI_VARTYPE_STR),
		"atemei_in"     => array(AOKI_VARTYPE_STR),
		"pera1_out"     => array(AOKI_VARTYPE_STR),
		"pera1_in"      => array(AOKI_VARTYPE_STR),
		"pera2_out"     => array(AOKI_VARTYPE_STR),
		"pera2_in"      => array(AOKI_VARTYPE_STR),
		"ayisatu_out"   => array(AOKI_VARTYPE_STR),
		"ayisatu_in"    => array(AOKI_VARTYPE_STR),
		"honjyou_out"   => array(AOKI_VARTYPE_STR),
		"honjyou_in"    => array(AOKI_VARTYPE_STR),
		"other1"        => array(AOKI_VARTYPE_STR),
		"other2"        => array(AOKI_VARTYPE_STR),
		"other3"        => array(AOKI_VARTYPE_STR),
		"other4"        => array(AOKI_VARTYPE_STR),
		"other5"        => array(AOKI_VARTYPE_STR)
	 );
	
	protected $_checks  = array(
		"begin_date"    => array( "EXIST" ),
		"end_date"      => array( "EXIST" ),
		"dm_date"       => array( "EXIST" ),
		"dm_no"         => array( "EXIST","NUM" ),
		"title"         => array( "EXIST" ),
		"sort"          => array( "EXIST" ),
	 );
	 
	 //maxlength 
	protected $_maxlens  = array(
		"dm_no "      =>  10 
	 );
	
	public function search($where ,&$page = NULL , $martrix_cd){	
	 	
		$sql = " SELECT a.*,c.scnt,c.ssum,(select  user_name FROM M_LOGIN where id  = a.u_user) as m_user_name from {$this->_table}  a 
				left join (select  dm_no,count(*) as scnt,sum(count)  as ssum from M_COST_DM_COUNT   where matrix_cd = {$martrix_cd} group by dm_no) c 
				on c.dm_no = a.dm_no   " ;
		return  parent::search($where ,$page ,$sql);
	}
	
	public function searchcsv($where ,&$page = NULL , $martrix_cd){	
	 	
		$sql = " SELECT a.*,c.count,c.shop_cd,c.shop_name, (select  user_name FROM M_LOGIN where id  = a.u_user) as m_user_name from {$this->_table}  a 
				left join  (select cc.shop_cd,cc.dm_no,cc.count,sp.shop_name from  M_COST_DM_COUNT cc    
				inner  join M_SHOP sp on sp.shop_cd = cc.shop_cd where cc.matrix_cd  = {$martrix_cd}) c 
				on c.dm_no = a.dm_no    " ;
				
		return  parent::search($where ,$page ,$sql);
	}
	
	public function  get_txt_data($where,$year4){
		$matable = "select  a.shop_cd, a.count ,dm.sort, substring(b.flier_date,5,2)  as month, 
		 (select  id from *M_COST_DM_PRICE*
		 where a.count >= num_from and a.count <=num_to and year4 = b.year4) as pid 
		  from M_COST_DM_COUNT a 
		  inner join  M_COST_DM dm on a.matrix_cd = dm.matrix_cd and a.dm_no = dm.dm_no 
		  inner  join M_MATRIX_TERM b on a.matrix_cd = b.matrix_cd where ".implode(' AND ', $where) ;
		  
		$sql = " select ma.shop_cd,ma.month,
			  sum(ma.count * (CASE when ma.sort= 1 then mb.teigata 
		      when ma.sort= 2 then mb.vatutyaku 
		      when ma.sort= 3 then mb.zatutyaku
		      when ma.sort= 4 then mb.fuusyo
		      when ma.sort= 5 then mb.tp
		      else 0 end) ) as money
			 from ({$matable} )  ma
			  inner join  *M_COST_DM_PRICE* mb on ma.pid = mb.id and mb.year4 = '{$year4}'
			  group by ma.month,ma.shop_cd order by ma.month,ma.shop_cd";
			  
		try {
			$limit = "";
		
			$this->_db->query( $sql );
			
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
}

	

?>